package com.sxfq.loansupermarket.backsystem.modular.guestDimension.service.impl;

import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.MembersUser;
import com.sxfq.loansupermarket.backsystem.modular.guestDimension.entity.GuestDimensionInfo;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.guestDimension.service.IGuestDimensionService;
import com.sxfq.loansupermarket.backsystem.modular.weixin.config.WebChatConfig;
import com.sxfq.loansupermarket.backsystem.modular.weixin.entity.WxTokenInfo;
import com.sxfq.loansupermarket.backsystem.modular.weixin.service.impl.MyX509TrustManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.TrustManager;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.ConnectException;
import java.net.URL;
import java.util.List;

@Service
@Transactional
public class GuestDimensionServiceImpl implements IGuestDimensionService {
    @Autowired
    private SqlMapper sqlMapper;

    public int insertWxChatRecord(GuestDimensionInfo guestDimensionInfo) throws Exception {
        String insql = "INSERT INTO `wx_chat_record` (`open_id`, `sender`,`receiver`, `chat_record`," +
                "`send_status`,`is_new`) VALUES" +
                "('" + guestDimensionInfo.getOpenId() + "', '" + guestDimensionInfo.getSender() + "'," +
                "'" + guestDimensionInfo.getReceiver() + "','" + guestDimensionInfo.getChatRecord() + "'," +
                "'" + guestDimensionInfo.getSendStatus() + "','" + guestDimensionInfo.getIsNew() + "');";
        sqlMapper.insert(insql);
        return 1;
    }

    public List<GuestDimensionInfo> queryChatRecordBySender(String sender) {
        String selsql = "select b.reservations_id,c.simplename,b.name,b.phone,b.headimgurl,a.sender,a.receiver,a.chat_record,if(left(a.send_time,10)=left(now(),10),CONCAT('今天 ',right(a.send_time,9)),a.send_time) as sendTime,a.send_status " +
                "from wx_chat_record a left join members_user b on a.open_id=b.open_id left join dept c on c.abbreviation=b.abbreviation where a.sender='" + sender + "' or a.receiver='" + sender + "' " +
                " or a.open_id='" + sender + "' order by a.send_time asc;";
        List<GuestDimensionInfo> guestDimensionInfoList = sqlMapper.selectList(selsql, GuestDimensionInfo.class);
        String upsql = "update wx_chat_record set is_new=0 where sender='" + sender + "' or receiver='" + sender + "'";
        sqlMapper.update(upsql);
        return guestDimensionInfoList;
    }

    public List<GuestDimensionInfo> fuzzyQuery(String sender) {
        String selsql = "select b.name,b.phone from members_user b where b.name  like '%" + sender + "%'  or b.phone like '%" + sender + "%';";
        List<GuestDimensionInfo> guestDimensionInfoList = sqlMapper.selectList(selsql, GuestDimensionInfo.class);
        return guestDimensionInfoList;
    }

    public List<GuestDimensionInfo> queryChatRecordByReceiver(String receiver) {
        String selsql = "select b.name,b.phone,a.sender,a.receiver,a.chat_record,a.send_time from wx_chat_record a left join members_user b on a.open_id=b.open_id  where a.receiver='" + receiver + "' order by a.send_time desc;";
        List<GuestDimensionInfo> guestDimensionInfoList = sqlMapper.selectList(selsql, GuestDimensionInfo.class);
        return guestDimensionInfoList;
    }

    public List<GuestDimensionInfo> queryChatRecordBySendTime(String startSenderTime, String endSenderTime) {
        String selsql = "select b.name,b.phone,a.sender,a.receiver,a.chat_record,a.send_time from wx_chat_record a left join members_user b on a.open_id=b.open_id  where a.send_time >='" + startSenderTime + "' " +
                "and a.send_time <='" + endSenderTime + "' order by a.send_time desc;";
        List<GuestDimensionInfo> guestDimensionInfoList = sqlMapper.selectList(selsql, GuestDimensionInfo.class);
        return guestDimensionInfoList;
    }

    public List<GuestDimensionInfo> querySenderList() {
        String selsql = "select  open_id as openId, sum(is_new) as isNewCount,send_time as sendTime,name as name,phone as phone,headimgurl from (select a.is_new as is_new,a.open_id as open_id,a.send_time as send_time," +
                "if(b.name is null,'未知',b.name) as name,b.phone as phone,b.headimgurl from crmscs.wx_chat_record a left join members_user b on a.open_id=b.open_id GROUP BY a.id  order by a.send_time desc )  as t GROUP BY t.open_id " +
                "order by t.send_time desc";
        List<GuestDimensionInfo> guestDimensionInfoList = sqlMapper.selectList(selsql, GuestDimensionInfo.class);
        return guestDimensionInfoList;
    }

    public MembersUser getWxUserInfo(String openId) {
        String accessToken = getAccessToken();
        String URL = ("https://api.weixin.qq.com/cgi-bin/user/info?access_token=ACCESS_TOKEN&openid=OPENID&lang=zh_CN").replace("ACCESS_TOKEN", accessToken).replace("OPENID", openId);
        com.alibaba.fastjson.JSONObject jsonObject = httpRequest(URL, "GET", null);//发送请求
        // 如果请求成功
        if (null != jsonObject) {
            MembersUser membersUserInfo = new MembersUser();
            membersUserInfo.setNickname(jsonObject.getString("nickname"));
            membersUserInfo.setHeadimgurl(jsonObject.getString("headimgurl"));
            return membersUserInfo;
        } else {
            MembersUser membersUserInfo = new MembersUser();
            membersUserInfo.setNickname("未知");
            membersUserInfo.setHeadimgurl("https://pynnn.oss-cn-hangzhou.aliyuncs.com/yiya001.png");
            return membersUserInfo;
        }
    }

    public String getAccessToken() {
        String accessToken = "";
        String getTokenSql = "select access_token as accessToken from wx_parameter_info where app_id='" + WebChatConfig.APPID + "' limit 1";
        WxTokenInfo wxTokenInfo = sqlMapper.selectOne(getTokenSql, WxTokenInfo.class);
        if (wxTokenInfo == null) {
            accessToken = WebChatConfig.getToken(WebChatConfig.APPID, WebChatConfig.SECERT).getAccessToken();
            String updTokenSql = "update wx_parameter_info set access_token='" + accessToken + "' where app_id='" + WebChatConfig.APPID + "';";
            sqlMapper.update(updTokenSql);
        } else {
            if ("".equals(wxTokenInfo.getAccessToken()) || "null".equals(wxTokenInfo.getAccessToken())) {
                accessToken = WebChatConfig.getToken(WebChatConfig.APPID, WebChatConfig.SECERT).getAccessToken();
                String updTokenSql = "update wx_parameter_info set access_token='" + accessToken + "' where app_id='" + WebChatConfig.APPID + "';";
                sqlMapper.update(updTokenSql);
            }
            accessToken = wxTokenInfo.getAccessToken();
        }
        return accessToken;
    }

    private static com.alibaba.fastjson.JSONObject httpRequest(String requestUrl, String requestMethod, String outputStr) {
        com.alibaba.fastjson.JSONObject jsonObject = null;
        StringBuffer buffer = new StringBuffer();
        try {
            // 创建SSLContext对象，并使用我们指定的信任管理器初始化
            TrustManager[] tm = {new MyX509TrustManager()};
            SSLContext sslContext = SSLContext.getInstance("SSL", "SunJSSE");
            sslContext.init(null, tm, new java.security.SecureRandom());
            // 从上述SSLContext对象中得到SSLSocketFactory对象
            SSLSocketFactory ssf = sslContext.getSocketFactory();
            URL url = new URL(requestUrl);
            HttpsURLConnection httpUrlConn = (HttpsURLConnection) url.openConnection();
            httpUrlConn.setSSLSocketFactory(ssf);
            httpUrlConn.setDoOutput(true);
            httpUrlConn.setDoInput(true);
            httpUrlConn.setUseCaches(false);
            // 设置请求方式（GET/POST）
            httpUrlConn.setRequestMethod(requestMethod);
            if ("GET".equalsIgnoreCase(requestMethod))
                httpUrlConn.connect();
            // 当有数据需要提交时
            if (null != outputStr) {
                OutputStream outputStream = httpUrlConn.getOutputStream();
                // 注意编码格式，防止中文乱码
                outputStream.write(outputStr.getBytes("UTF-8"));
                outputStream.close();
            }
            // 将返回的输入流转换成字符串
            InputStream inputStream = httpUrlConn.getInputStream();
            InputStreamReader inputStreamReader = new InputStreamReader(inputStream, "utf-8");
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String str = null;
            while ((str = bufferedReader.readLine()) != null) {
                buffer.append(str);
            }
            bufferedReader.close();
            inputStreamReader.close();
            // 释放资源
            inputStream.close();
            inputStream = null;
            httpUrlConn.disconnect();
            jsonObject = com.alibaba.fastjson.JSONObject.parseObject(buffer.toString());
            // jsonObject = JSONObject.fromObject(buffer.toString());
        } catch (ConnectException ce) {
            System.out.println("Weixin server connection timed out.");
        } catch (Exception e) {
            System.out.println("https request error:{}" + e.getMessage());
        }
        return jsonObject;
    }
}